﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using JQueryDataTables.Models;

namespace TireBias.Models
{
    public class LossModel
    {
        public class LOSS_DS
        {
            public string _code { get; set; }
            public string _desc { get; set; }
            public string _act { get; set; }
            public string _cal { get; set; }
            public string _ModifyBy { get; set; }
            public string _ModifyDT { get; set; }
        }
        public class RES_LOSS_DS
        {
            public string _Biasno { get; set; }
            public string _Lsdate { get; set; }
            public string _Reason { get; set; }
            public string _Text { get; set; }
            public string _Empname { get; set; }
            public string _Shift { get; set; }
            public string _Start { get; set; }
            public string _End { get; set; }
            public string _LsTime { get; set; }
            public string _Topcode { get; set; }
            public string _Tcrcdlot { get; set; }
            public string _Toproll { get; set; }
        }

        public List<LOSS_DS> SEL_LOSS_MASTER(string code,string desc="")
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            string wh = " where ";
            str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.LOSS_REASON  ");
            if (StrUtil.IsNotEmpty(code))
            {
                str.Append(wh + "  ls_code='" + code + "'");
                wh = " and ";
            }
            if (StrUtil.IsNotEmpty(desc))
            {
                str.Append(wh + "  ls_desc='" + desc + "'");
            }


            SqlCommand cmd = new SqlCommand(str.ToString() + " order by convert(int,ls_code)", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;


            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<LOSS_DS> list = new List<LOSS_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                LOSS_DS model = new LOSS_DS();
                model._code = items["ls_code"].ToString();
                model._desc = items["ls_desc"].ToString();
                model._act = items["ls_act"].ToString();
                model._cal = items["ls_cal"].ToString();
                model._ModifyBy = items["ModifyBy"].ToString();
                model._ModifyDT = DateUtil.ToDisplayDateTime(items["ModifyDate"], DateUtil.eLanguage.EN);

                list.Add(model);
            }
            return list;
        }


        public Boolean Ins_Master(LOSS_DS incoming)
        {
            try
            {
                using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
                {
                    cnn.Open();
                    cnn.BeginTransaction();
                    StringBuilder comstr = new StringBuilder();
                    comstr.AppendLine(" INSERT INTO ");
                    comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[LOSS_REASON]");
                    comstr.AppendLine(" ([LS_CODE], [LS_DESC], [LS_ACT],[LS_CAL],[modifyby],Modifydate) ");
                    comstr.AppendLine("  VALUES (@code,@desc,@act,@cal, @empname,getdate())");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();
                    cmd.AddParameterStringWithValue("@code", incoming._code);
                    cmd.AddParameterStringWithValue("@desc", incoming._desc);
                    cmd.AddParameterStringWithValue("@act", incoming._act);
                    cmd.AddParameterStringWithValue("@cal", incoming._cal);
                    cmd.AddParameterStringWithValue("@empname", incoming._ModifyBy);


                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                    cnn.CommitTransaction();
                    return true;
                }

            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
            }

        }

        public Boolean UPD_Master(LOSS_DS incoming)
        {
            try
            {
                using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
                {
                    cnn.Open();
                    cnn.BeginTransaction();
                    StringBuilder comstr = new StringBuilder();
                    comstr.AppendLine(" UPDATE ");
                    comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[LOSS_REASON]");
                    comstr.AppendLine(" SET [LS_DESC]=@desc, [LS_ACT]=@act  ,[LS_CAL]=@cal,[modifyby]=@empname,Modifydate=getdate() ");
                    comstr.AppendLine(" Where [LS_CODE]=@code ");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();
                    cmd.AddParameterStringWithValue("@code", incoming._code);
                    cmd.AddParameterStringWithValue("@desc", incoming._desc);
                    cmd.AddParameterStringWithValue("@cal", incoming._cal);
                    cmd.AddParameterStringWithValue("@act", incoming._act);
                    cmd.AddParameterStringWithValue("@empname", incoming._ModifyBy);


                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                    cnn.CommitTransaction();
                    return true;
                }

            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
            }

        }



        public List<RES_LOSS_DS> SEL_LOSS(string dt, JQueryDataTableParamModel para, string shift, string biasno,string topcode,string toplot,string toproll)
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select a.LS_CODE, CONVERT(VARCHAR(5), a.LS_START, 108) LS_START, CONVERT(VARCHAR(5), a.LS_END, 108) LS_END ");
            str.AppendLine(",Topcrdcd,Tcrcdlot,toproll, case when LS_CAL is null then '' else ((DATEDIFF ( mi , ls_start , ls_end )*60)/100) end LOSS  ");
        
            str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASLOSS a ");
            str.AppendLine(" INNER JOIN " + Nemo.GetINI("Dbase", "Base") + ".dbo.LOSS_REASON b  on a.ls_code=b.ls_code  ");
            str.AppendLine(" where a.ls_date=convert(datetime,@dt,120)  ");

            if (StrUtil.IsNotEmpty(shift))
            {
                str.AppendLine(" and a.shift = @shift ");
            }
            if (StrUtil.IsNotEmpty(biasno))
            {
                str.AppendLine(" and a.biasno = @biasno ");
            }

            if (StrUtil.IsNotEmpty(topcode))
            {
                str.AppendLine(" and a.Topcrdcd = @Topcrdcd ");
            }
            if (StrUtil.IsNotEmpty(toplot))
            {
                str.AppendLine(" and a.Tcrcdlot = @toplot ");
            }
            if (StrUtil.IsNotEmpty(toproll))
            {
                str.AppendLine(" and a.toproll = @toproll ");
            }
            SqlCommand cmd = new SqlCommand(str.ToString() + " ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dt, WinNemoLib.DateUtil.eLanguage.EN);
            if (StrUtil.IsNotEmpty(para.sSearch)) cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = para.sSearch + "%";
            if (StrUtil.IsNotEmpty(shift)) cmd.Parameters.Add("@shift", SqlDbType.VarChar).Value = shift;
            if (StrUtil.IsNotEmpty(biasno)) cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;
            if (StrUtil.IsNotEmpty(topcode)) cmd.Parameters.Add("@Topcrdcd", SqlDbType.VarChar).Value = topcode;
            if (StrUtil.IsNotEmpty(toplot)) cmd.Parameters.Add("@toplot", SqlDbType.VarChar).Value = toplot;
            if (StrUtil.IsNotEmpty(toproll)) cmd.Parameters.Add("@toproll", SqlDbType.VarChar).Value = toproll;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<RES_LOSS_DS> list = new List<RES_LOSS_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                RES_LOSS_DS model = new RES_LOSS_DS();
                model._Start = items["LS_START"].ToString();
                model._End =  items["LS_END"].ToString();                
                model._Reason = items["LS_CODE"].ToString();
                model._LsTime = items["LOSS"].ToString();
                model._Topcode = items["Topcrdcd"].ToString();
                model._Tcrcdlot = items["Tcrcdlot"].ToString();
                model._Toproll = items["toproll"].ToString();
                list.Add(model);
            }
            return list;
        }

        public Boolean Ins_Data(RES_LOSS_DS incoming)
        {
            try
            {
                using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
                {
                    cnn.Open();
                    cnn.BeginTransaction();
                    StringBuilder comstr = new StringBuilder();
                    comstr.AppendLine(" INSERT INTO ");
                    comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASLOSS]");
                    comstr.AppendLine(" ([BIASNO], [LS_date],[LS_Start],[LS_END], [LS_CODE] ,[modifyby],Modifydate,shift,text, [Topcrdcd],Tcrcdlot,Toproll) ");
                    comstr.AppendLine("  VALUES (@biasno,convert(datetime, @LS_date,120), convert(datetime, @start,120),convert(datetime, @end,120), @reason, @empname,getdate(),@shift,@text, @topcode,@Tcrcdlot,@toproll)");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();



                    cmd.AddParameterStringWithValue("@LS_date", DateUtil.ToInternalDateTime(incoming._Lsdate.Trim(), WinNemoLib.DateUtil.eLanguage.EN));
                    cmd.AddParameterStringWithValue("@biasno", incoming._Biasno);
                    cmd.AddParameterStringWithValue("@topcode", incoming._Topcode);
                    cmd.AddParameterStringWithValue("@Tcrcdlot", incoming._Tcrcdlot);
                    cmd.AddParameterStringWithValue("@toproll", incoming._Toproll);
                    cmd.AddParameterStringWithValue("@reason", incoming._Reason);
                    cmd.AddParameterStringWithValue("@empname", incoming._Empname);
                    cmd.AddParameterStringWithValue("@shift", incoming._Shift.Trim());
                    cmd.AddParameterStringWithValue("@end", DateUtil.ToInternalDate(incoming._Lsdate.Trim(), WinNemoLib.DateUtil.eLanguage.EN) + " " + incoming._End);
                    cmd.AddParameterStringWithValue("@start", DateUtil.ToInternalDate(incoming._Lsdate.Trim(), WinNemoLib.DateUtil.eLanguage.EN) + " " + incoming._Start);
                    cmd.AddParameterStringWithValue("@text", incoming._Text);

                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                    cnn.CommitTransaction();
                    return true;
                }

            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
            }

        }

        public DataTable SEL_LOSS_DT(string dt, string shift, string biasno,string topcode, string lot, string roll)
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select a.LS_CODE,b.LS_DESC, CONVERT(VARCHAR(5), a.LS_START, 108) LS_START, CONVERT(VARCHAR(5), a.LS_END, 108) LS_END ");
            str.AppendLine(" , DATEDIFF ( mi , ls_start , ls_end )  LOSS,[text] ");
            str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASLOSS a ");
            str.AppendLine(" INNER JOIN " + Nemo.GetINI("Dbase", "Base") + ".dbo.LOSS_REASON b  on a.ls_code=b.ls_code  ");
            str.AppendLine(" where a.ls_date=convert(datetime,@dt,120) and biasno=@biasno ");

            if (StrUtil.IsNotEmpty(lot)) str.AppendLine(" and a.tcrcdlot=@lot ");
            if (StrUtil.IsNotEmpty(topcode)) str.AppendLine(" and a.Topcrdcd=@topcode ");
            if (StrUtil.IsNotEmpty(roll)) str.AppendLine(" and a.toproll=@roll ");

            if (StrUtil.IsNotEmpty(shift))
            {
                str.AppendLine(" and a.shift = @shift ");
            }
            if (StrUtil.IsNotEmpty(biasno))
            {
                str.AppendLine(" and a.biasno = @biasno ");
            }


            SqlCommand cmd = new SqlCommand(str.ToString() + " ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dt, WinNemoLib.DateUtil.eLanguage.EN);

            if (StrUtil.IsNotEmpty(topcode)) cmd.Parameters.Add("@topcode", SqlDbType.VarChar).Value = topcode;
            if (StrUtil.IsNotEmpty(roll)) cmd.Parameters.Add("@roll", SqlDbType.VarChar).Value = roll;
            if (StrUtil.IsNotEmpty(lot)) cmd.Parameters.Add("@lot", SqlDbType.VarChar).Value = lot;
            if (StrUtil.IsNotEmpty(shift)) cmd.Parameters.Add("@shift", SqlDbType.VarChar).Value = shift;
            if (StrUtil.IsNotEmpty(biasno)) cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<RES_LOSS_DS> list = new List<RES_LOSS_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return null;
            }

            return ds.Tables[0];
        }

    }
}